﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QLDBHYT.VO;
using System.Data;
using System.Data.SqlClient;


namespace QLDBHYT.DAO
{
    public class PhieuNhapDAO
    {
        private dbConnection conn;
        public PhieuNhapDAO()
        {
            conn = new dbConnection();
        }
        public DataTable searchAll(int thang, int nam,string condition)
        {
            string query = string.Format("select stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx,ktc, bietduoc,nhomthuoc,"
            + "\r\n" +  "   isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
            + "\r\n" +  "   sum(sldauky) + sum(slnhap) - sum(slxuat) - sum(slxuatdc) as slcuoiky"
            + "\r\n" +  "   from ("
            + "\r\n" +  "   select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,a.ktc, "
            + "\r\n" +  "   a.duongdung as bietduoc,a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, " 
            + "\r\n" +  "   isnull(c.sldauky,0) as sldauky,isnull(c.slnhap,0) as slnhap,0 as slxuat,isnull(slxuatdc,0) as slxuatdc "
            + "\r\n" +  "   from dm a left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu   "
            + "\r\n" +  "   left join NT_PHIEUNHAP c on a.stt_nh = c.stt_nh and a.ma_hieu = c.ma_hieu and c.thang = @thang and c.nam = @nam "
            + "\r\n" +  "   where  b.gia <> 0 " + condition + " group by a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh,  "
            + "\r\n" +  "   c.sldauky,c.slnhap,slxuatdc,a.duongdung,a.lieudung,a.ktc "  
            + "\r\n" +  "   union all "
            + "\r\n" +  "   select a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,d.ktc, "
            + "\r\n" +  "   d.duongdung as bietduoc,d.lieudung as nhomthuoc,isnull(e.gia,0) as gia,isnull(e.gia_bhxh,0) as gia_bhxh, "
            + "\r\n" +  "   0 as sldauky,0 slnhap,sum(sl) as slxuat,0 as slxuatdc "
            + "\r\n" +  "   from p1_h b inner join p1_d a on a.ct_id = b.ct_id  "
            + "\r\n" +  "   inner join dm d on d.ma_hieu = a.ma_hieu  "
            + "\r\n" + "    left join gia e on d.stt_nh = e.stt_nh and d.ma_hieu = e.ma_hieu where  a.stt_nh in ('01','03') and  "
            + "\r\n" + "    ngay_tt >= (select dauky from nt_ky where ky = 1) " + condition 
            + "\r\n" +  "   group by a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,e.gia,e.gia_bhxh,d.ktc, "
            + "\r\n" +  "   d.duongdung,d.lieudung "
            + "\r\n" +  "   union all  "
            + "\r\n" +  "   select a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,d.ktc, "
            + "\r\n" +  "   d.duongdung as bietduoc,d.lieudung as nhomthuoc,isnull(e.gia,0) as gia,isnull(e.gia_bhxh,0) as gia_bhxh,  "
            + "\r\n" +  "   0 as sldauky,0 slnhap,sum(sl) as slxuat,0 as slxuatdc  "
            + "\r\n" +  "   from p2_h b inner join p2_d a on a.ct_id = b.ct_id  "
            + "\r\n" +  "   inner join dm d on d.ma_hieu = a.ma_hieu  "
             + "\r\n" + "   left join gia e on d.stt_nh = e.stt_nh and d.ma_hieu = e.ma_hieu where  a.stt_nh in ('01','03') and  "
            + "\r\n" +  "   ngay_tt >= (select dauky from nt_ky where ky = 1) " + condition 
            + "\r\n" +  "   group by a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,e.gia,e.gia_bhxh,d.ktc, "
            + "\r\n" +  "   d.duongdung,d.lieudung "
            + "\r\n" +  "   ) as temps "
            + "\r\n" +  "   group by stt_nh,ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx,gia,gia_bhxh,ktc, "
            + "\r\n" + "    bietduoc,nhomthuoc "
            + "\r\n" +  "   order by ten_hieu");
            SqlParameter[] sqlParameters = new SqlParameter[2];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = Convert.ToInt32(thang);
            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = Convert.ToInt32(nam);
           
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchPhieuXuatTHByKy(int thang, int nam, string nhomPC,string tungay,string denngay, string condition)
        {
            string query = string.Format("select stt,ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx,ngay_tt," 
            + "\r\n" +  "   sldauky,sum(slnhap) as slnhap,sum(slxuat) as slxuat,gia,gia_bhxh"
            + "\r\n" +  "   from ( "
            + "\r\n" +  "   select 1 as stt, d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,b.ngay_tt, 0 as sldauky,0 as slnhap,"
            + "\r\n" +  "   sum(sl) as slxuat,isnull(e.gia,0) as gia,isnull(e.gia_bhxh,0) as gia_bhxh "
            + "\r\n" +  "   from  p1_h b inner join p1_d c on b.ct_id = c.ct_id "
            + "\r\n" +  "   inner join dm d on d.stt_nh = c.stt_nh and d.ma_hieu = c.ma_hieu"
            + "\r\n" +  "   inner join gia e on d.stt_nh = e.stt_nh and d.ma_hieu = e.ma_hieu  "
            + "\r\n" + "   where  c.stt_nh = @nhompc and ngay_tt >= '" + tungay + "' and ngay_tt <= '" + denngay + "'" + condition
            + "\r\n" +  "   group by d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,b.ngay_tt,e.gia,e.gia_bhxh  "
            + "\r\n" +  "   union all  "
            + "\r\n" +  "   select 1 as stt,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,b.ngay_tt, 0 as sldauky,0 as slnhap,"
            + "\r\n" +  "   sum(sl) as slxuat,isnull(e.gia,0) as gia,isnull(e.gia_bhxh,0) as gia_bhxh "
            + "\r\n" +  "   from p2_h b inner join p2_d c on b.ct_id = c.ct_id "
            + "\r\n" +  "   inner join dm d on d.stt_nh = c.stt_nh and d.ma_hieu = c.ma_hieu "
            + "\r\n" +  "   inner join gia e on d.stt_nh = e.stt_nh and d.ma_hieu = e.ma_hieu"
            + "\r\n" + "   where  c.stt_nh = @nhompc and ngay_tt >= '" + tungay + "' and ngay_tt <= '" + denngay + "'" + condition
            + "\r\n" + "   group by d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,b.ngay_tt,e.gia,e.gia_bhxh  "
            + " Union all  "
            + " select 1 as stt, d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,a.ngaynhap "
            + " as ngay_tt,0 as sldauky,slnhap,  "
            + " 0 as slxuat,g.gia,g.gia_bhxh from  "
            + " NT_PHIEUNHAP_DTL a  left join dm d on d.ma_hieu = a.ma_hieu   "
            + " left join gia g on g.ma_hieu = a.ma_hieu "
            + " where a.stt_nh = @nhompc and a.thang = @thang and a.nam = @nam  " + condition
            + " union all   "
            + " select 0 as stt,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx, "
            + " b.dauky as ngay_tt,sldauky,0 as slnhap,   "
            + " 0 as slxuat,gia,gia_bhxh from nt_phieunhap a   "
            + " left join  nt_ky b on a.thang = b.thang and a.nam = b.nam   "
            + " left join dm d on d.ma_hieu = a.ma_hieu   "
            + " left join gia g on g.ma_hieu = a.ma_hieu "
            + " where a.stt_nh = @nhompc and a.thang = @thang and a.nam = @nam and sldauky <> 0  " + condition
            + " Union all "
            + " select 1 as stt,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,a.ngayxuat as ngay_tt,"
            + " 0 as sldauky,0 as slnhap,slxuat,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh from "
            + " nt_phieuxuat_dc a "
            + " inner join dm d on d.ma_hieu = a.ma_hieu "
            + " left join gia g on g.ma_hieu = a.ma_hieu "
            + " where  a.stt_nh = @nhompc and a.thang = @thang and a.nam = @nam " + condition
            + " ) as tam "
            + " group by stt,ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx,ngay_tt, "
            + " sldauky,gia,gia_bhxh "
            + " order by ma_hieu,ten_hieu,stt,ngay_tt");

            SqlParameter[] sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = Convert.ToInt32(thang);
            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = Convert.ToInt32(nam);
            sqlParameters[2] = new SqlParameter("@nhompc", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(nhomPC);
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchPhieuXuatByKy(int thang, int nam, string nhomPC,string tungay,string denngay, string condition)
        {
            //string query = string.Format("select d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,b.ngay_tt,b.sophieu,"
            //+ " 0 as sldauky,0 as slnhap,sl as slxuat,isnull(e.gia,0) as gia,isnull(e.gia_bhxh,0) as gia_bhxh from "
            //+ " p1_h b inner join p1_d c on c.ct_id = b.ct_id "
            //+ " inner join dm d on d.ma_hieu = c.ma_hieu "
            //+ " inner join gia e on d.stt_nh = e.stt_nh and d.ma_hieu = e.ma_hieu  "
            //+ " where  c.stt_nh = @nhompc and ngay_tt >= '" + tungay + "' and ngay_tt <= '" + denngay + "'" + condition
            //+ " Union all"
            //+ " select d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,b.ngay_tt,b.sophieu,"
            //+ " 0 as sldauky,0 as slnhap,sl as slxuat,isnull(e.gia,0) as gia,isnull(e.gia_bhxh,0) as gia_bhxh from "
            //+ " p2_h b inner join p2_d c on c.ct_id = b.ct_id "
            //+ " inner join dm d on d.ma_hieu = c.ma_hieu "
            //+ " inner join gia e on d.stt_nh = e.stt_nh and d.ma_hieu = e.ma_hieu  "
            //+ " where  c.stt_nh = @nhompc and ngay_tt >= '" + tungay + "' and ngay_tt <= '" + denngay + "'" + condition
            //+ " Union all "
            //+ " select  d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,a.ngaynhap as ngay_tt,'phiÕu nhËp' as sophieu,0 as sldauky,slnhap,"
            //+ " 0 as slxuat,0 as gia,0 as gia_bhxh from NT_PHIEUNHAP_DTL a "
            //+ " left join dm d on d.ma_hieu = a.ma_hieu "
            //+ " where a.stt_nh = @nhompc and a.thang = @thang and a.nam = @nam " + condition
            //+ " union all "
            //+ " select d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,b.dauky as ngay_tt,'' as sophieu,sldauky,0 as slnhap, "
            //+ " 0 as slxuat,0 as gia,0 as gia_bhxh from nt_phieunhap a "
            //+ " left join  nt_ky b on a.thang = b.thang and a.nam = b.nam "
            //+ " left join dm d on d.ma_hieu = a.ma_hieu "
            //+ " where a.stt_nh = @nhompc and a.thang = @thang and a.nam = @nam " + condition
            //+ " Union all "
            //+ " select d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,a.ngayxuat as ngay_tt,'XuÊt ®iÒu chØnh' as sophieu,"
            //+ " 0 as sldauky,0 as slnhap,slxuat,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh from "
            //+ " nt_phieuxuat_dc a "
            //+ " inner join dm d on d.ma_hieu = a.ma_hieu "
            //+ " left join gia g on g.ma_hieu = a.ma_hieu "
            //+ " where  a.stt_nh = @nhompc and a.thang = @thang and a.nam = @nam and slxuat <> 0 " + condition
            //+ " order by d.ma_hieu,ten_hieu,b.ngay_tt,b.sophieu");

            string query = string.Format("select 1 as stt,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,'' as losanxuat,'' as handung,b.ngay_tt,b.sophieu,"
            + " 0 as sldauky,0 as slnhap,sl as slxuat,isnull(e.gia,0) as gia,isnull(e.gia_bhxh,0) as gia_bhxh from "
            + " p1_h b inner join p1_d c on c.ct_id = b.ct_id "
            + " inner join dm d on d.ma_hieu = c.ma_hieu "
            + " inner join gia e on d.stt_nh = e.stt_nh and d.ma_hieu = e.ma_hieu  "
            + " where  c.stt_nh = @nhompc and ngay_tt >= '" + tungay + "' and ngay_tt <= '" + denngay + "'" + condition
            + " Union all"
            + " select 2 as stt,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,'' as losanxuat,'' as handung,b.ngay_tt,b.sophieu,"
            + " 0 as sldauky,0 as slnhap,sl as slxuat,isnull(e.gia,0) as gia,isnull(e.gia_bhxh,0) as gia_bhxh from "
            + " p2_h b inner join nt_p2_d_detail c on c.ct_id = b.ct_id "
            + " inner join dm d on d.ma_hieu = c.ma_hieu "
            + " inner join gia e on d.stt_nh = e.stt_nh and d.ma_hieu = e.ma_hieu  "
            + " where  c.stt_nh = @nhompc and c.ngay >= '" + tungay + "' and c.ngay <= '" + denngay + "'" + condition
            + " Union all "
            + " select 0 as stt, d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,a.losanxuat,a.handung,"
            + " case when a.dauky = 1 then b.dauky else a.ngaynhap end as ngay_tt, "
            + " case when a.dauky = 1 then '§Çu kú' else 'phiÕu nhËp' end as sophieu,"
            + " case when a.dauky = 1 then slnhap else 0 end as sldauky, case when a.dauky = 0 then slnhap else 0 end as slnhap,"
            + " 0 as slxuat,0 as gia,0 as gia_bhxh from NT_PHIEUNHAP_DTL a "
            + " left join dm d on d.ma_hieu = a.ma_hieu "
            + " left join  nt_ky b on a.thang = b.thang and a.nam = b.nam "
            + " where a.stt_nh = @nhompc and a.thang = @thang and a.nam = @nam " + condition
            + " Union all "
            + " select 2 as stt, d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,'' as losanxuat,'' as handung,a.ngayxuat as ngay_tt,'XuÊt ®iÒu chØnh' as sophieu,"
            + " 0 as sldauky,0 as slnhap,slxuat,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh from "
            + " nt_phieuxuat_dc a "
            + " inner join dm d on d.ma_hieu = a.ma_hieu "
            + " left join gia g on g.ma_hieu = a.ma_hieu "
            + " where  a.stt_nh = @nhompc and a.thang = @thang and a.nam = @nam and slxuat <> 0 " + condition
            + " order by d.ma_hieu,ten_hieu,b.ngay_tt,stt,b.sophieu");


            SqlParameter[] sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = Convert.ToInt32(thang);
            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int); 
            sqlParameters[1].Value = Convert.ToInt32(nam);
            sqlParameters[2] = new SqlParameter("@nhompc", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(nhomPC);
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchPhieuXuatSoSachByKy(int thang, int nam, string nhomPC, string condition)
        {
            string query = string.Format("select ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx,ngay_tt,"
            + " sophieu,sum(slxuat) as slxuat,sum(sl) as sl,gia,gia_bhxh from ("
            + " select d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,b.ngay_tt,b.sophieu,slxuat,0 as sl,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh from "
            + " nt_phieuxuat a inner join p1_h b on a.ct_id = b.ct_id "
            + " inner join p1_d c on c.ct_id = a.ct_id and c.ct_id0 = a.ct_id0 "
            + " inner join dm d on d.ma_hieu = a.ma_hieu "
            + " where  a.stt_nh = @nhompc and a.thang = @thang and a.nam = @nam " + condition
            + " Union all"
            + " select d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,b.ngay_tt,b.sophieu,slxuat,0 as sl,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh from "
            + " nt_phieuxuat a inner join p2_h b on a.ct_id = b.ct_id "
            + " inner join p2_d c on c.ct_id = a.ct_id and c.ct_id0 = a.ct_id0 "
            + " inner join dm d on d.ma_hieu = a.ma_hieu "
            + " where  a.stt_nh = @nhompc and a.thang = @thang and a.nam = @nam " + condition
            + " union all "
            + " select d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,b.ngay_tt, "
            + " b.sophieu,0 as slxuat,sl,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh from " 
            + " p1_h b inner join p1_d a on a.ct_id = b.ct_id "
            + " inner join dm d on d.ma_hieu = a.ma_hieu  where  a.stt_nh =  @nhompc " + condition
            + " and ngay_tt >= (select dauky from nt_ky where ky = 1) "
            + " union all "
            + " select d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,b.ngay_tt, "
            + " b.sophieu,0 as slxuat,sl,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh from "  
            + " p2_h b inner join p2_d a on a.ct_id = b.ct_id "
            + " inner join dm d on d.ma_hieu = a.ma_hieu  where  a.stt_nh = @nhompc" + condition
            + " and ngay_tt >= (select dauky from nt_ky where ky = 1) "
            + " ) as temp "
            + " group by ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx,ngay_tt, "
            + " sophieu,gia,gia_bhxh "
            + " order by ma_hieu,ten_hieu,ngay_tt,sophieu");

            SqlParameter[] sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = Convert.ToInt32(thang);
            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = Convert.ToInt32(nam);
            sqlParameters[2] = new SqlParameter("@nhompc", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(nhomPC);
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchByNgay(int thang, int nam, string nhomPC, string tungay, string denngay, string condition)
        {
            //string query = string.Format("select stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx, bietduoc,nhomthuoc,gia,gia_bhxh, "
            //   + "\r\n" +  " sum(sldauky) as sldauky,sum(slnhap) as slnhap,sum(slxuat) as slxuat,sum(slxuatdc) as slxuatdc,"
            //   + "\r\n" +  " sum(sldauky)  + sum(slnhap) - sum(slxuat) - sum(slxuatdc) as slcuoiky"
            //   + "\r\n" +  "   from ("
            //   + "\r\n" +  "   select stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx, bietduoc,nhomthuoc,gia,gia_bhxh, "
            //   + "\r\n" +  "   sum(sldauky)  + sum(slnhap) - sum(slxuat) - sum(slxuatdc) as sldauky,0 as slnhap,0 as slxuat,0 as slxuatdc,0 as slcuoiky "
            //   + "\r\n" +  "   from ("
            //   + "\r\n" +  "   select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc,"
            //   + "\r\n" +  "   a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
            //   + "\r\n" +  "   isnull(c.sldauky,0)as sldauky,isnull(sum(g.slnhap),0) as slnhap,0 as slxuat,0 as slxuatdc,0 as slcuoiky from dm a "
            //   + "\r\n" +  "   left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1 "
            //   + "\r\n" +  "   left join NT_PHIEUNHAP c on a.stt_nh = c.stt_nh and a.ma_hieu = c.ma_hieu and c.thang = @thang and c.nam = @nam "
            //   + "\r\n" +  "   left join NT_PHIEUNHAP_DTL g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam "
            //   + "\r\n" +  "   and ngaynhap < @tungay "
            //   + "\r\n" +  "   where  a.stt_nh = '01'  and a.chon = 1 and b.gia <> 0  group by a.stt_nh, "
            //   + "\r\n" +  "   a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh, c.sldauky,a.duongdung,a.lieudung"
            //   + "\r\n" +  "   Union all"
            //   + "\r\n" +  "   select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc,"
            //   + "\r\n" +  "   a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
            //   + "\r\n" +  "   0 as sldauky,0 as slnhap,isnull(sum(slxuat),0) as slxuat,0 as slxuatdc,0 as slcuoiky from dm a "
            //   + "\r\n" +  "   left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1 "
            //   + "\r\n" +  "   left join NT_PHIEUXUAT g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam "
            //   + "\r\n" +  "   and ngay_xuat < @tungay "
            //   + "\r\n" +  "   where  a.stt_nh = '01'  and a.chon = 1 and b.gia <> 0  group by a.stt_nh, "
            //   + "\r\n" +  "   a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh,a.duongdung,a.lieudung"
            //   + "\r\n" +  "   Union all"
            //   + "\r\n" +  "   select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc,"
            //   + "\r\n" +  "   a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
            //   + "\r\n" +  "   0 as sldauky,0 as slnhap,0 as slxuat,isnull(sum(g.slxuat),0)  as slxuatdc,0 as slcuoiky from dm a "
            //   + "\r\n" +  "   left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1 "
            //   + "\r\n" +  "   left join NT_PHIEUXUAT_DC g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam "
            //   + "\r\n" +  "   and ngayxuat < @tungay "
            //   + "\r\n" +  "   where  a.stt_nh = '01'  and a.chon = 1 and b.gia <> 0  group by a.stt_nh, "
            //   + "\r\n" +  "   a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh,a.duongdung,a.lieudung "
            //   + "\r\n" +  "   ) as tam "
            //   + "\r\n" +  "   group by stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx, bietduoc,nhomthuoc,gia,gia_bhxh "
            //   + "\r\n" +  "   union all "
            //   + "\r\n" +  "   select stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx, bietduoc,nhomthuoc,gia,gia_bhxh, "
            //   + "\r\n" + "    0 as sldauky,sum(slnhap) as slnhap,sum(slxuat) as slxuat,sum(slxuatdc) as slxuatdc,0 as slcuoiky "
            //   + "\r\n" +  "   from ( "
            //   + "\r\n" +  "   select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc, "
            //   + "\r\n" +  "   a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
            //   + "\r\n" +  "   0 as sldauky,isnull(sum(g.slnhap),0) as slnhap,0 as slxuat,0 as slxuatdc,0 as slcuoiky from dm a "
            //   + "\r\n" +  "   left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1 "
            //   + "\r\n" +  "   left join NT_PHIEUNHAP_DTL g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam "
            //   + "\r\n" +  "   and ngaynhap >= @tungay and ngaynhap <= @denngay"
            //   + "\r\n" +  "   where  a.stt_nh = '01'  and a.chon = 1 and b.gia <> 0  group by a.stt_nh, "
            //   + "\r\n" +  "   a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh, a.duongdung,a.lieudung "
            //   + "\r\n" +  "   Union all "
            //   + "\r\n" +  "   select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc, "
            //   + "\r\n" +  "   a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh,  "
            //   + "\r\n" +  "   0 as sldauky,0 as slnhap,isnull(sum(slxuat),0) as slxuat,0 as slxuatdc,0 as slcuoiky from dm a  "
            //   + "\r\n" +  "   left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1  "
            //   + "\r\n" +  "   left join NT_PHIEUXUAT g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam  "
            //   + "\r\n" +  "   and ngay_xuat >= @tungay and ngay_xuat <= @denngay "
            //   + "\r\n" +  "   where  a.stt_nh = '01'  and a.chon = 1 and b.gia <> 0  group by a.stt_nh,  "
            //   + "\r\n" +  "   a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh,a.duongdung,a.lieudung "
            //   + "\r\n" +  "   Union all "
            //   + "\r\n" +  "   select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc, "
            //   + "\r\n" +  "   a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh,  "
            //   + "\r\n" +  "   0 as sldauky,0 as slnhap,0 as slxuat,isnull(sum(g.slxuat),0)  as slxuatdc,0 as slcuoiky from dm a  "
            //   + "\r\n" +  "   left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1  "
            //   + "\r\n" +  "   left join NT_PHIEUXUAT_DC g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam  "
            //   + "\r\n" +  "   and ngayxuat >= @tungay and ngayxuat <= @denngay  "
            //   + "\r\n" +  "   where  a.stt_nh = '01'  and a.chon = 1 and b.gia <> 0  group by a.stt_nh,  "
            //   + "\r\n" +  "   a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh,a.duongdung,a.lieudung "
            //   + "\r\n" +  "   ) as tam "
            //   + "\r\n" +  "   group by stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx, bietduoc,nhomthuoc,gia,gia_bhxh "
            //   + "\r\n" +  "   ) as tam1  "
            //   + "\r\n" +  "   group by stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx, bietduoc,nhomthuoc,gia,gia_bhxh");

            string query = string.Format("select stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx,bietduoc,nhomthuoc, gia, gia_bhxh,"
            + " sum(sldauky) as sldauky,sum(slnhap) as slnhap,sum(slxuat)as slxuat, sum(slxuatdc) as slxuatdc, "
            + " sum(sldauky) + sum(slnhap) - sum(slxuat) - sum(slxuatdc) as slcuoiky "
            + " from ("
            + " select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, "
            + " a.duongdung as bietduoc,a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
            + " isnull(c.sldauky,0) as sldauky,isnull(sum(g.slnhap),0) as slnhap,0 as slxuat,0 as slxuatdc"
            + " from dm a left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1 "
            + " left join NT_PHIEUNHAP c on a.stt_nh = c.stt_nh and a.ma_hieu = c.ma_hieu and c.thang = " + thang + " and c.nam = " + nam
            + " left join NT_PHIEUNHAP_DTL g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = c.thang and g.nam = c.nam "
            + " where  a.stt_nh in " + nhomPC + " and a.chon = 1 and b.gia <> 0  " + condition 
            + " group by a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh, "
            + " c.sldauky,c.slnhap,slxuatdc,a.duongdung,a.lieudung"
            + " union all "
            + " select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc,"
            + " a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
            + " 0 as sldauky,0 as slnhap,0 as slxuat,isnull(sum(g.slxuat),0)  as slxuatdc from dm a "
            + " left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1 "
            + " left join NT_PHIEUXUAT_DC g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam "
            + " where  a.stt_nh in " + nhomPC + " and a.chon = 1 and b.gia <> 0 " + condition 
            + " group by a.stt_nh, "
            + " a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh,a.duongdung,a.lieudung "
            + " Union all "
            + " select a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,"
            + " d.duongdung as bietduoc,d.lieudung as nhomthuoc,isnull(e.gia,0) as gia,isnull(e.gia_bhxh,0) as gia_bhxh,"
            + " 0 as sldauky,0 slnhap,sum(sl) as slxuat,0 as slxuatdc"
            + " from p1_h b inner join p1_d a on a.ct_id = b.ct_id "
            + " inner join dm d on d.ma_hieu = a.ma_hieu  "
            + " inner join gia e on d.stt_nh = e.stt_nh and d.ma_hieu = e.ma_hieu  where  a.stt_nh in  " + nhomPC 
            + " and ngay_tt >= (select dauky from nt_ky where thang = " + thang + " and nam = " + nam + ") and "
            + " ngay_tt <= (select cuoiky from nt_ky where thang = " + thang + " and nam = " + nam + ")" + condition 
            + " group by a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,e.gia,e.gia_bhxh,"
            + " d.duongdung,d.lieudung"
            + " union all "
            + " select a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,"
            + " d.duongdung as bietduoc,d.lieudung as nhomthuoc,isnull(e.gia,0) as gia,isnull(e.gia_bhxh,0) as gia_bhxh, "
            + " 0 as sldauky,0 slnhap,sum(sl) as slxuat,0 as slxuatdc "
            + " from p2_h b inner join p2_d a on a.ct_id = b.ct_id "
            + " inner join dm d on d.ma_hieu = a.ma_hieu "
            + " inner join gia e on d.stt_nh = e.stt_nh and d.ma_hieu = e.ma_hieu  where  a.stt_nh in " + nhomPC 
            + " and ngay_tt >= (select dauky from nt_ky where thang = " + thang + " and nam = " + nam + ") and "
            + " ngay_tt <= (select cuoiky from nt_ky where thang = " + thang + " and nam = " + nam + ")"  + condition 
            + " group by a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,e.gia,e.gia_bhxh,"
            + " d.duongdung,d.lieudung"
            + " ) as temps"
            + " group by "
            + " stt_nh,ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx,gia,gia_bhxh,"
            + " bietduoc,nhomthuoc");
            

            SqlParameter[] sqlParameters = new SqlParameter[5];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = Convert.ToInt32(thang);
            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = Convert.ToInt32(nam);
            sqlParameters[2] = new SqlParameter("@nhompc", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(nhomPC);
            sqlParameters[3] = new SqlParameter("@tungay", SqlDbType.NVarChar);
            sqlParameters[3].Value = tungay;
            sqlParameters[4] = new SqlParameter("@denngay", SqlDbType.NVarChar);
            sqlParameters[4].Value = denngay;
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchByKy(int thang,int nam,string nhomPC,string denngay,string condition)
        {
            //string query = string.Format("select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,"
            //+ " a.duongdung as bietduoc,a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh,"
            //+ " isnull(c.sldauky,0) as sldauky,isnull(c.slnhap,0) as slnhap,isnull(sum(slxuat),0) as slxuat,isnull(slxuatdc,0) as slxuatdc,"
            //+ " isnull(c.sldauky,0) + isnull(c.slnhap,0) - isnull(sum(slxuat),0) - isnull(slxuatdc,0) as slcuoiky"
            //+ " from dm a left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1"
            //+ " left join NT_PHIEUNHAP c on a.stt_nh = c.stt_nh and a.ma_hieu = c.ma_hieu"
            //+ " and c.thang = @thang and c.nam = @nam"
            //+ " left join NT_PHIEUXUAT d on a.stt_nh = d.stt_nh and a.ma_hieu = d.ma_hieu"
            //+ " and d.thang = @thang and d.nam = @nam "
            //+ " where  a.stt_nh = @nhompc  and a.chon = 1 and b.gia <> 0 " + condition 
            //+ " group by a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh,"
            //+ " c.sldauky,c.slnhap,slxuatdc,a.duongdung,a.lieudung");

            string query = string.Format("select stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx,bietduoc,nhomthuoc, gia, gia_bhxh,"
            + " sum(sldauky) as sldauky,sum(slnhap) - sum(sldauky) as slnhap,sum(slxuat)as slxuat, sum(slxuatdc) as slxuatdc, "
            + " sum(slnhap) - sum(slxuat) - sum(slxuatdc) as slcuoiky "
            + " from ("
            + " select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, "
            + " a.duongdung as bietduoc,a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
            + " isnull(c.sldauky,0) as sldauky,isnull(sum(g.slnhap),0) as slnhap,0 as slxuat,0 as slxuatdc"
            + " from dm a left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1 "
            + " left join NT_PHIEUNHAP c on a.stt_nh = c.stt_nh and a.ma_hieu = c.ma_hieu and c.thang = " + thang + " and c.nam = " + nam
            + " left join NT_PHIEUNHAP_DTL g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = c.thang and g.nam = c.nam "
            + " and ngaynhap <= '" + denngay + "'"
            + " where  a.stt_nh in " + nhomPC + " and a.chon = 1 and b.gia <> 0  " + condition
            + " group by a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh, "
            + " c.sldauky,c.slnhap,slxuatdc,a.duongdung,a.lieudung"
            + " union all "
            + " select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc,"
            + " a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
            + " 0 as sldauky,0 as slnhap,0 as slxuat,isnull(sum(g.slxuat),0)  as slxuatdc from dm a "
            + " left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1 "
            + " left join NT_PHIEUXUAT_DC g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam "
            + " and ngayxuat <= '" + denngay + "'"
            + " where  a.stt_nh in " + nhomPC + " and a.chon = 1 and b.gia <> 0 " + condition
            + " group by a.stt_nh, "
            + " a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh,a.duongdung,a.lieudung "
            + " union all "
            + " select a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,"
            + " d.duongdung as bietduoc,d.lieudung as nhomthuoc,isnull(e.gia,0) as gia,isnull(e.gia_bhxh,0) as gia_bhxh," 
            + " 0 as sldauky,0 slnhap,sum(sl) as slxuat,0 as slxuatdc"
            + " from p1_h b inner join p1_d a on a.ct_id = b.ct_id "
            + " inner join dm d on d.ma_hieu = a.ma_hieu "
            + " inner join gia e on d.stt_nh = e.stt_nh and d.ma_hieu = e.ma_hieu where  a.stt_nh in  " + nhomPC 
            + " and ngay_tt >= (select dauky from nt_ky where ky = 1) "
            + " and ngay_tt <= '" + denngay + "'" + condition
            + " group by a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,e.gia,e.gia_bhxh,"
            + " d.duongdung,d.lieudung"
            + " union all "
            + " select a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,"
            + " d.duongdung as bietduoc,d.lieudung as nhomthuoc,isnull(e.gia,0) as gia,isnull(e.gia_bhxh,0) as gia_bhxh, "
            + " 0 as sldauky,0 slnhap,sum(sl) as slxuat,0 as slxuatdc "
            + " from p2_h b inner join p2_d a on a.ct_id = b.ct_id "
            + " inner join dm d on d.ma_hieu = a.ma_hieu "
            + " inner join gia e on d.stt_nh = e.stt_nh and d.ma_hieu = e.ma_hieu where  a.stt_nh in " + nhomPC 
            + " and ngay_tt >= (select dauky from nt_ky where ky = 1) "
            + " and ngay_tt <= '" + denngay + "'" + condition
            + " group by a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,e.gia,e.gia_bhxh,"
            + " d.duongdung,d.lieudung"
            + " ) as temps"
            + " group by "
            + " stt_nh,ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx,gia,gia_bhxh,"
            + " bietduoc,nhomthuoc");



            SqlParameter[] sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = Convert.ToInt32(thang);
            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = Convert.ToInt32(nam);
            sqlParameters[2] = new SqlParameter("@nhompc", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(nhomPC);
           
            return conn.executeSelectQuery(query, sqlParameters);
         }
        public DataTable SearchNgayNhap(int thang, int nam, string mahieu)
        {
           string query = string.Format("select ngaynhap,slnhap,losanxuat,handung from NT_PHIEUNHAP_DTL "
            + " WHERE ma_hieu = @mahieu and thang = @thang and nam = @nam and slnhap <> 0 and dauky = 0  ");

            SqlParameter[] sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = Convert.ToInt32(thang);
            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = Convert.ToInt32(nam);
            sqlParameters[2] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(mahieu);
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchNgayXuat(int thang, int nam, string mahieu)
        {
            string query = string.Format("select ngayxuat,slxuat from NT_PHIEUXUAT_DC "
            + " WHERE ma_hieu = @mahieu and thang = @thang and nam = @nam and slxuat <> 0    ");

            SqlParameter[] sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = Convert.ToInt32(thang);
            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = Convert.ToInt32(nam);
            sqlParameters[2] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(mahieu);
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchSLNhap(int thang, int nam, string mahieu)
        {
            string query = string.Format("select * from NT_PHIEUNHAP_DTL "
            + " where thang = @thang and nam = @nam and ma_hieu = @mahieu and slnhap <> 0 "
            + " ORDER BY ngaynhap DESC, KEYSL DESC ");

            SqlParameter[] sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = Convert.ToInt32(thang);
            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = Convert.ToInt32(nam);
            sqlParameters[2] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(mahieu);
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchSLDauKy(int thang, int nam)
        {
            string query = string.Format("select * from NT_PHIEUNHAP "
            + " where thang = @thang and nam = @nam and sldauky > 0 ");
           
            SqlParameter[] sqlParameters = new SqlParameter[2];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = Convert.ToInt32(thang);
            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = Convert.ToInt32(nam);
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable SearchSLXuat(int thang, int nam, string mahieu)
        {
            string query = string.Format("select ngay_tt as ngay_xuat,sl as slxuat,sophieu from "
            + " p1_h b inner join p1_d a on a.ct_id = b.ct_id"
            + " WHERE ma_hieu = @mahieu and ngay_tt >= "
            + " (select dauky from nt_ky where thang = @thang  and nam = @nam ) "
            + " union all"
            + " select ngay_tt as ngay_xuat,sl as slxuat,sophieu from  "
            + " p2_h b inner join p2_d a on a.ct_id = b.ct_id"
            + " WHERE ma_hieu = @mahieu and ngay_tt >= "
            + " (select dauky from nt_ky where thang = @thang  and nam = @nam) "
            + " order by ngay_tt ");

            SqlParameter[] sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = Convert.ToInt32(thang);
            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = Convert.ToInt32(nam);
            sqlParameters[2] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[2].Value = Convert.ToString(mahieu);
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public bool InsertRecorDTL(PhieunhapVO phieunhapVO)
        {
            string query = string.Format("Insert into NT_PHIEUNHAP_DTL(keysl,stt_nh,ma_hieu,thang,nam,ngaynhap,slnhap,losanxuat,handung,dauky) "
            + " VALUES (@keysl,@sttnh,@mahieu,@thang,@nam,@ngaynhap,@slnhap,@losanxuat,@handung,@dauky)");
            SqlParameter[] sqlParameters = new SqlParameter[10];

            sqlParameters[0] = new SqlParameter("@sttnh", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(phieunhapVO.Sttnh);

            sqlParameters[1] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(phieunhapVO.Mahieu);

            sqlParameters[2] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[2].Value = phieunhapVO.Thang;

            sqlParameters[3] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[3].Value = phieunhapVO.Nam;

            sqlParameters[4] = new SqlParameter("@ngaynhap", SqlDbType.DateTime);
            sqlParameters[4].Value = phieunhapVO.Ngaynhap;

            sqlParameters[5] = new SqlParameter("@slnhap", SqlDbType.Decimal);
            sqlParameters[5].Value = phieunhapVO.Sl;

            sqlParameters[6] = new SqlParameter("@losanxuat", SqlDbType.NVarChar);
            sqlParameters[6].Value = phieunhapVO.Losanxuat;

            sqlParameters[7] = new SqlParameter("@handung", SqlDbType.DateTime);
            sqlParameters[7].Value = phieunhapVO.Handung;

            sqlParameters[8] = new SqlParameter("@keysl", SqlDbType.NVarChar);
            sqlParameters[8].Value = phieunhapVO.Keysl;

            sqlParameters[9] = new SqlParameter("@dauky", SqlDbType.SmallInt);
            sqlParameters[9].Value = phieunhapVO.Dauky;

           return  conn.executeInsertQuery(query, sqlParameters);
        }
        public bool InsertRecorDC(PhieunhapVO phieunhapVO)
        {
            string query = string.Format("Insert into NT_PHIEUXUAT_DC(stt_nh,ma_hieu,thang,nam,Ngayxuat,slxuat) "
            + " VALUES (@sttnh,@mahieu,@thang,@nam,@ngayxuat,@slxuat)");
            SqlParameter[] sqlParameters = new SqlParameter[6];

            sqlParameters[0] = new SqlParameter("@sttnh", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(phieunhapVO.Sttnh);

            sqlParameters[1] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(phieunhapVO.Mahieu);

            sqlParameters[2] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[2].Value = phieunhapVO.Thang;

            sqlParameters[3] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[3].Value = phieunhapVO.Nam;

            sqlParameters[4] = new SqlParameter("@ngayxuat", SqlDbType.DateTime);
            sqlParameters[4].Value = phieunhapVO.Ngayxuat;

            sqlParameters[5] = new SqlParameter("@slxuat", SqlDbType.Decimal);
            sqlParameters[5].Value = phieunhapVO.Slx;

            return conn.executeInsertQuery(query, sqlParameters);
        }
        public PhieunhapVO InsertRecord(PhieunhapVO phieunhapVO)
        {

            string query = string.Format("Insert into NT_PHIEUNHAP(stt_nh,ma_hieu,thang,nam,sldauky,slnhap,slxuatdc) "
            + " VALUES (@sttnh,@mahieu,@thang,@nam,@sldauky,@slnhap,@slxuatdc)");
            SqlParameter[] sqlParameters = new SqlParameter[7];

            sqlParameters[0] = new SqlParameter("@sttnh", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(phieunhapVO.Sttnh);

            sqlParameters[1] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(phieunhapVO.Mahieu);

            sqlParameters[2] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[2].Value = phieunhapVO.Thang;

            sqlParameters[3] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[3].Value = phieunhapVO.Nam;

            sqlParameters[4] = new SqlParameter("@sldauky", SqlDbType.Decimal);
            sqlParameters[4].Value = phieunhapVO.Sldauky;

            sqlParameters[5] = new SqlParameter("@slnhap", SqlDbType.Decimal);
            sqlParameters[5].Value = phieunhapVO.Slnhap;

            sqlParameters[6] = new SqlParameter("@slxuatdc", SqlDbType.Decimal);
            sqlParameters[6].Value = phieunhapVO.Slxuatdc;

                  
            conn.executeInsertQuery(query, sqlParameters);
            //insert bang chi tiet
            InsertRecorDTL(phieunhapVO);

            return phieunhapVO;

        }
        public bool UpdateDM(PhieunhapVO phieunhapVO)
        {
            string query = string.Format("UPDATE DM SET "
            + "lieudung = @nhomthuoc,duongdung = @bietduoc "
            + "where  stt_nh = @sttnh and ma_hieu = @mahieu ");

           
            SqlParameter[] sqlParameters = new SqlParameter[4];

            sqlParameters[0] = new SqlParameter("@sttnh", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(phieunhapVO.Sttnh);

            sqlParameters[1] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(phieunhapVO.Mahieu);

            sqlParameters[2] = new SqlParameter("@nhomthuoc", SqlDbType.NVarChar);
            sqlParameters[2].Value = phieunhapVO.Nhomthuoc;

            sqlParameters[3] = new SqlParameter("@bietduoc", SqlDbType.NVarChar);
            sqlParameters[3].Value = phieunhapVO.Bietduoc;


            conn.executeInsertQuery(query, sqlParameters);
            return true;
        }
        public bool UpdateRecordDTL(PhieunhapVO phieunhapVO)
        {
            string query = string.Format("UPDATE NT_PHIEUNHAP_DTL SET "
            + "slnhap = slnhap + @slnhap "
            + "where  ma_hieu = @mahieu and ngaynhap= @ngaynhap and losanxuat = @losanxuat and handung = @handung ");

            SqlParameter[] sqlParameters = new SqlParameter[5];

            sqlParameters[0] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[0].Value = phieunhapVO.Mahieu;

            sqlParameters[1] = new SqlParameter("@ngaynhap", SqlDbType.DateTime);
            sqlParameters[1].Value = phieunhapVO.Ngaynhap;

            sqlParameters[2] = new SqlParameter("@slnhap", SqlDbType.Decimal);
            sqlParameters[2].Value = phieunhapVO.Sl;

            sqlParameters[3] = new SqlParameter("@losanxuat", SqlDbType.NVarChar);
            sqlParameters[3].Value = phieunhapVO.Losanxuat;

            sqlParameters[4] = new SqlParameter("@handung", SqlDbType.DateTime);
            sqlParameters[4].Value = phieunhapVO.Handung;

            conn.executeInsertQuery(query, sqlParameters);
            return true;
        }
        public bool UpdateRecordDC(PhieunhapVO phieunhapVO)
        {
            string query = string.Format("UPDATE NT_PHIEUXUAT_DC SET "
            + "slxuat = slxuat + @slxuat "
            + "where  ma_hieu = @mahieu and ngayxuat= @ngayxuat ");

            SqlParameter[] sqlParameters = new SqlParameter[3];

            sqlParameters[0] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[0].Value = phieunhapVO.Mahieu;

            sqlParameters[1] = new SqlParameter("@ngayxuat", SqlDbType.DateTime);
            sqlParameters[1].Value = phieunhapVO.Ngayxuat;

            sqlParameters[2] = new SqlParameter("@slxuat", SqlDbType.Decimal);
            sqlParameters[2].Value = phieunhapVO.Slx;

            conn.executeInsertQuery(query, sqlParameters);
            return true;
        }
        public bool UpdateRecord(PhieunhapVO phieunhapVO)
        {

            string query = string.Format("UPDATE NT_PHIEUNHAP SET "
            + "slnhap = @slnhap,slxuatdc = @slxuatdc "
            + "where  stt_nh = @sttnh and ma_hieu = @mahieu and thang= @thang and nam = @nam ");
            
            SqlParameter[] sqlParameters = new SqlParameter[6];

            sqlParameters[0] = new SqlParameter("@sttnh", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(phieunhapVO.Sttnh);

            sqlParameters[1] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(phieunhapVO.Mahieu);

            sqlParameters[2] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[2].Value = phieunhapVO.Thang;

            sqlParameters[3] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[3].Value = phieunhapVO.Nam;
                       
            sqlParameters[4] = new SqlParameter("@slnhap", SqlDbType.Decimal);
            sqlParameters[4].Value = phieunhapVO.Slnhap;

            sqlParameters[5] = new SqlParameter("@slxuatdc", SqlDbType.Decimal);
            sqlParameters[5].Value = phieunhapVO.Slxuatdc;
            if (phieunhapVO.Sl != 0)
            {
                if (CheckPhieuNhapDTL(phieunhapVO).Rows.Count == 0)
                {
                    InsertRecorDTL(phieunhapVO);
                }
                else 
                {
                    UpdateRecordDTL(phieunhapVO);
                }
            }
            if (phieunhapVO.Slx != 0)
            {
                if (CheckPhieuXuatDC(phieunhapVO).Rows.Count == 0)
                {
                    InsertRecorDC(phieunhapVO);
                }
                else
                {
                    UpdateRecordDC(phieunhapVO);
                }
            }           
            conn.executeInsertQuery(query, sqlParameters);
            return true ;

        }
        public bool DeleteRecordDTL(PhieunhapVO phieunhapVO)
        {
            string query = string.Format("Delete NT_PHIEUNHAP_DTL"
            + "where  ma_hieu = @mahieu and ngaynhap= @ngaynhap ");

            SqlParameter[] sqlParameters = new SqlParameter[2];

            sqlParameters[0] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[0].Value = phieunhapVO.Mahieu;

            sqlParameters[1] = new SqlParameter("@ngaynhap", SqlDbType.DateTime);
            sqlParameters[1].Value = phieunhapVO.Ngaynhap;

            conn.executeInsertQuery(query, sqlParameters);
            return true;
        }
        public bool DeleteRecordDC(PhieunhapVO phieunhapVO)
        {
            string query = string.Format("Delete NT_PHIEUXUAT_DC"
            + "where  ma_hieu = @mahieu and ngayxuat= @ngayxuat ");

            SqlParameter[] sqlParameters = new SqlParameter[2];

            sqlParameters[0] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[0].Value = phieunhapVO.Mahieu;

            sqlParameters[1] = new SqlParameter("@ngayxuat", SqlDbType.DateTime);
            sqlParameters[1].Value = phieunhapVO.Ngayxuat;

            conn.executeInsertQuery(query, sqlParameters);
            return true;
        }
        public DataTable SlXuat(PhieunhapVO phieunhapVO)
        {
            string query = string.Format("select isnull(sum(slxuat),0) as slxuat from dbo.NT_phieuxuat where "
            + " stt_nh = @sttnh and ma_hieu = @mahieu and thang= @thang and nam = @nam");

            SqlParameter[] sqlParameters = new SqlParameter[4];

            sqlParameters[0] = new SqlParameter("@sttnh", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(phieunhapVO.Sttnh);

            sqlParameters[1] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(phieunhapVO.Mahieu);

            sqlParameters[2] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[2].Value = phieunhapVO.Thang;

            sqlParameters[3] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[3].Value = phieunhapVO.Nam;

            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable CheckPhieuNhapDTL(PhieunhapVO phieunhapVO)
        {
            string query = string.Format("select * from dbo.NT_PHIEUNHAP_DTL where "
            + " ma_hieu = @mahieu and ngaynhap = @ngaynhap and losanxuat = @losanxuat and handung = @handung");

            SqlParameter[] sqlParameters = new SqlParameter[4];

            sqlParameters[0] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[0].Value = phieunhapVO.Mahieu  ;

            sqlParameters[1] = new SqlParameter("@ngaynhap", SqlDbType.SmallDateTime);
            sqlParameters[1].Value = phieunhapVO.Ngaynhap ;

            sqlParameters[2] = new SqlParameter("@losanxuat", SqlDbType.NVarChar);
            sqlParameters[2].Value = phieunhapVO.Losanxuat;

            sqlParameters[3] = new SqlParameter("@handung", SqlDbType.DateTime);
            sqlParameters[3].Value = phieunhapVO.Handung;
                     

                        
            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable CheckPhieuXuatDC(PhieunhapVO phieunhapVO)
        {
            string query = string.Format("select * from dbo.NT_PHIEUXUAT_DC where "
            + " ma_hieu = @mahieu and ngayxuat = @ngayxuat");

            SqlParameter[] sqlParameters = new SqlParameter[2];

            sqlParameters[0] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[0].Value = phieunhapVO.Mahieu;

            sqlParameters[1] = new SqlParameter("@ngayxuat", SqlDbType.SmallDateTime);
            sqlParameters[1].Value = phieunhapVO.Ngayxuat;

            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable CheckPhieuXuat(int thang,int nam)
        {

            string query = string.Format("select * from dbo.NT_PhieuXuat where "
            + " thang= @thang and nam = @nam");

            SqlParameter[] sqlParameters = new SqlParameter[2];

            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = thang;

            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = nam;

            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable CheckPhieuNhap(PhieunhapVO phieunhapVO)
        {
            string query = string.Format("select * from dbo.NT_PHIEUNHAP where "
            + " stt_nh = @sttnh and ma_hieu = @mahieu and thang= @thang and nam = @nam");

            SqlParameter[] sqlParameters = new SqlParameter[4];

            sqlParameters[0] = new SqlParameter("@sttnh", SqlDbType.NVarChar);
            sqlParameters[0].Value = Convert.ToString(phieunhapVO.Sttnh);

            sqlParameters[1] = new SqlParameter("@mahieu", SqlDbType.NVarChar);
            sqlParameters[1].Value = Convert.ToString(phieunhapVO.Mahieu);

            sqlParameters[2] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[2].Value = phieunhapVO.Thang;

            sqlParameters[3] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[3].Value = phieunhapVO.Nam;

            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable Soluongxuat(string stt_nh,string ma_hieu, int thang, int nam, string tungay, string denngay)
        {
            string query = string.Format("select SUM(slxuat) as slxuat from"
             + "\r\n" + "  (select sl as slxuat from  p1_h b "
             + "\r\n" + "  inner join p1_d c on c.ct_id = b.ct_id"
             + "\r\n" + "  where  ngay_tt >= '" + tungay + "'"
             + "\r\n" + "  and ngay_tt <= '" + denngay + "' and c.stt_nh = '" + stt_nh + "' and c.ma_hieu = '" + ma_hieu + "'"
             + "\r\n" + "  Union all "
             + "\r\n" + "  select sl as slxuat from  "
             + "\r\n" + "  p2_h b inner join p2_d c on c.ct_id = b.ct_id  "
             + "\r\n" + "  where  ngay_tt >= '" + tungay + "'"
             + "\r\n" + "  and ngay_tt <= '" + denngay + "' and c.stt_nh = '" + stt_nh + "' and c.ma_hieu = '" + ma_hieu + "'"
             + "\r\n" + "  Union all  "
             + "\r\n" + "  select slxuat from  "
             + "\r\n" + "  nt_phieuxuat_dc a where  a.stt_nh = '" + stt_nh + "' "
             + "\r\n" + "  and a.thang = " + thang + " and a.nam = " + nam + " and slxuat <> 0 "
             + "\r\n" + "  and a.ma_hieu = '" + ma_hieu + "'"
             + "\r\n" + "  ) as t");

            SqlParameter[] sqlParameters = new SqlParameter[0];
            return conn.executeSelectQuery(query, sqlParameters);
            

        }
        public bool Chuyenky(int thang,int nam,string tungay,string denngay)
        {
            int thangmoi = thang + 1;
            int nammoi = nam;
            if (thangmoi == 13)
            {
                thangmoi = 1;
                nammoi = nammoi + 1;
            }
                       
                string query = string.Format("insert into NT_PHIEUNHAP"
                + "\r\n" + "  select stt_nh, ma_hieu,@thangmoi,@nammoi, "
                + "\r\n" + " sum(sldauky),sum(slnhap), sum(slxuatdc)"
                + "\r\n" + "   from ("
                + "\r\n" + "   select stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx, bietduoc,nhomthuoc,gia,gia_bhxh, "
                + "\r\n" + "   sum(slnhap) - sum(slxuat) - sum(slxuatdc) as sldauky,0 as slnhap,0 as slxuat,0 as slxuatdc "
                + "\r\n" + "   from ("
                + "\r\n" + "   select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc,"
                + "\r\n" + "   a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
                + "\r\n" + "   isnull(c.sldauky,0)as sldauky,isnull(sum(g.slnhap),0) as slnhap,0 as slxuat,0 as slxuatdc from dm a "
                + "\r\n" + "   left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1 "
                + "\r\n" + "   left join NT_PHIEUNHAP c on a.stt_nh = c.stt_nh and a.ma_hieu = c.ma_hieu and c.thang = @thang and c.nam = @nam "
                + "\r\n" + "   left join NT_PHIEUNHAP_DTL g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam "
                + "\r\n" + "   and ngaynhap <= @denngay "
                + "\r\n" + "   where  a.stt_nh in ('01','03')  and a.chon = 1 and b.gia <> 0  group by a.stt_nh, "
                + "\r\n" + "   a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh, c.sldauky,a.duongdung,a.lieudung"
                + "\r\n" + "   Union all"
                + "\r\n" + "   select a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx, d.duongdung as bietduoc,"
                + "\r\n" + "   d.lieudung as nhomthuoc,isnull(e.gia,0) as gia,isnull(e.gia_bhxh,0) as gia_bhxh, 0 as sldauky,"
                + "\r\n" + "   0 slnhap,sum(sl) as slxuat,0 as slxuatdc from p1_h b inner join p1_d a on a.ct_id = b.ct_id  "
                + "\r\n" + "   inner join dm d on d.ma_hieu = a.ma_hieu "
                + "\r\n" + "   inner join gia e on d.stt_nh = e.stt_nh and d.ma_hieu = e.ma_hieu where  a.stt_nh in ('01','03') and "
                + "\r\n" + "   ngay_tt >= (select dauky from nt_ky where ky = 1) and "
                + "\r\n" + "   ngay_tt <= @denngay "
                + "\r\n" + "   group by a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,e.gia,e.gia_bhxh, "
                + "\r\n" + "   d.duongdung,d.lieudung "
                + "\r\n" + "   union all  "
                + "\r\n" + "   select a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx, "
                + "\r\n" + "   d.duongdung as bietduoc,d.lieudung as nhomthuoc,isnull(e.gia,0) as gia,"
                + "\r\n" + "   isnull(e.gia_bhxh,0) as gia_bhxh,  0 as sldauky,0 slnhap,sum(sl) as slxuat,0 as slxuatdc  "
                + "\r\n" + "   from p2_h b inner join p2_d a on a.ct_id = b.ct_id  inner join dm d on d.ma_hieu = a.ma_hieu " 
                + "\r\n" + "   inner join gia e on d.stt_nh = e.stt_nh and d.ma_hieu = e.ma_hieu "
                + "\r\n" + "   where  a.stt_nh in ('01','03') and "
                + "\r\n" + "   ngay_tt >= (select dauky from nt_ky where ky = 1) and "
                + "\r\n" + "   ngay_tt <= @denngay "
                + "\r\n" + "   group by a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,e.gia,e.gia_bhxh, "
                + "\r\n" + "   d.duongdung,d.lieudung "
                + "\r\n" + "   Union all "
                + "\r\n" + "   select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc,"
                + "\r\n" + "   a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
                + "\r\n" + "   0 as sldauky,0 as slnhap,0 as slxuat,isnull(sum(g.slxuat),0)  as slxuatdc from dm a "
                + "\r\n" + "   left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1 "
                + "\r\n" + "   left join NT_PHIEUXUAT_DC g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam "
                + "\r\n" + "   and ngayxuat <= @denngay "
                + "\r\n" + "   where  a.stt_nh in ('01','03')  and a.chon = 1 and b.gia <> 0  group by a.stt_nh, "
                + "\r\n" + "   a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh,a.duongdung,a.lieudung "
                + "\r\n" + "   ) as tam "
                + "\r\n" + "   group by stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx, bietduoc,nhomthuoc,gia,gia_bhxh "
                + "\r\n" + "   union all "
                + "\r\n" + "   select stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx, bietduoc,nhomthuoc,gia,gia_bhxh, "
                + "\r\n" + "    0 as sldauky,sum(slnhap) as slnhap,sum(slxuat) as slxuat,sum(slxuatdc) as slxuatdc "
                + "\r\n" + "   from ( "
                + "\r\n" + "   select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc, "
                + "\r\n" + "   a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
                + "\r\n" + "   0 as sldauky,isnull(sum(g.slnhap),0) as slnhap,0 as slxuat,0 as slxuatdc from dm a "
                + "\r\n" + "   left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1 "
                + "\r\n" + "   left join NT_PHIEUNHAP_DTL g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam "
                + "\r\n" + "   and ngaynhap >= @tungay "
                + "\r\n" + "   where  a.stt_nh in ('01','03')  and a.chon = 1 and b.gia <> 0  group by a.stt_nh, "
                + "\r\n" + "   a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh, a.duongdung,a.lieudung "
                + "\r\n" + "   Union all "
                + "\r\n" + "   select a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx, d.duongdung as bietduoc,"
                + "\r\n" + "   d.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, 0 as sldauky,"
                + "\r\n" + "   0 slnhap,sum(sl) as slxuat,0 as slxuatdc from p1_h b inner join p1_d a on a.ct_id = b.ct_id  "
                + "\r\n" + "   inner join dm d on d.ma_hieu = a.ma_hieu  where  a.stt_nh in ('01','03') and "
                + "\r\n" + "   ngay_tt >= @tungay "
                + "\r\n" + "   group by a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,gia,gia_bhxh, "
                + "\r\n" + "   d.duongdung,d.lieudung "
                + "\r\n" + "   union all  "
                + "\r\n" + "   select a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx, "
                + "\r\n" + "   d.duongdung as bietduoc,d.lieudung as nhomthuoc,isnull(gia,0) as gia,"
                + "\r\n" + "   isnull(gia_bhxh,0) as gia_bhxh,  0 as sldauky,0 slnhap,sum(sl) as slxuat,0 as slxuatdc  "
                + "\r\n" + "   from p2_h b inner join p2_d a on a.ct_id = b.ct_id  inner join dm d on d.ma_hieu = a.ma_hieu "
                + "\r\n" + "   where  a.stt_nh in ('01','03') and "
                + "\r\n" + "   ngay_tt >= @tungay "
                + "\r\n" + "   group by a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,gia,gia_bhxh, "
                + "\r\n" + "   d.duongdung,d.lieudung "
                + "\r\n" + "   Union all "
                + "\r\n" + "   select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc, "
                + "\r\n" + "   a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh,  "
                + "\r\n" + "   0 as sldauky,0 as slnhap,0 as slxuat,isnull(sum(g.slxuat),0)  as slxuatdc from dm a  "
                + "\r\n" + "   left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1  "
                + "\r\n" + "   left join NT_PHIEUXUAT_DC g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam  "
                + "\r\n" + "   and ngayxuat >= @tungay "
                + "\r\n" + "   where  a.stt_nh in ('01','03')  and a.chon = 1 and b.gia <> 0  group by a.stt_nh,  "
                + "\r\n" + "   a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh,a.duongdung,a.lieudung "
                + "\r\n" + "   ) as tam "
                + "\r\n" + "   group by stt_nh, ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx, bietduoc,nhomthuoc,gia,gia_bhxh "
                + "\r\n" + "   ) as tam1  "
                + "\r\n" + "   group by stt_nh, ma_hieu");

            SqlParameter[] sqlParameters = new SqlParameter[6];

            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value =thang;

            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = nam;

            sqlParameters[2] = new SqlParameter("@thangmoi", SqlDbType.Int);
            sqlParameters[2].Value = thangmoi;

            sqlParameters[3] = new SqlParameter("@nammoi", SqlDbType.Int);
            sqlParameters[3].Value = nammoi;

            sqlParameters[4] = new SqlParameter("@tungay", SqlDbType.NVarChar);
            sqlParameters[4].Value = tungay;

            sqlParameters[5] = new SqlParameter("@denngay", SqlDbType.NVarChar);
            sqlParameters[5].Value = denngay;
                        
            conn.executeInsertQuery(query, sqlParameters);

            /*up date phieu nhap*/
            query = string.Format("UPDATE NT_PHIEUNHAP_DTL SET THANG = @thangmoi,nam = @nammoi "
                + " where ngaynhap >= @tungay ");

            sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@thangmoi", SqlDbType.Int);
            sqlParameters[0].Value = thangmoi;

            sqlParameters[1] = new SqlParameter("@nammoi", SqlDbType.Int);
            sqlParameters[1].Value = nammoi;

            sqlParameters[2] = new SqlParameter("@tungay", SqlDbType.NVarChar);
            sqlParameters[2].Value = tungay;
            conn.executeInsertQuery(query, sqlParameters);

            /*up date phieu xuat*/
            query = string.Format("UPDATE NT_PHIEUXUAT SET THANG = @thangmoi,nam = @nammoi "
                + " where ngay_xuat >= @tungay ");

            sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@thangmoi", SqlDbType.Int);
            sqlParameters[0].Value = thangmoi;

            sqlParameters[1] = new SqlParameter("@nammoi", SqlDbType.Int);
            sqlParameters[1].Value = nammoi;

            sqlParameters[2] = new SqlParameter("@tungay", SqlDbType.NVarChar);
            sqlParameters[2].Value = tungay;
            conn.executeInsertQuery(query, sqlParameters);
            /*up date phieu dieu chinh*/

            query = string.Format("UPDATE NT_PHIEUXUAT_DC SET THANG = @thangmoi,nam = @nammoi "
               + " where ngayxuat >= @tungay ");

            sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@thangmoi", SqlDbType.Int);
            sqlParameters[0].Value = thangmoi;

            sqlParameters[1] = new SqlParameter("@nammoi", SqlDbType.Int);
            sqlParameters[1].Value = nammoi;

            sqlParameters[2] = new SqlParameter("@tungay", SqlDbType.NVarChar);
            sqlParameters[2].Value = tungay;
            conn.executeInsertQuery(query, sqlParameters);

            /*Insert Ky*/
            query = string.Format("insert into nt_ky(thang,nam,ky,dauky) "
            + " values(@thangmoi,@nammoi,1,@tungay)");
            sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@thangmoi", SqlDbType.Int);
            sqlParameters[0].Value = thangmoi;

            sqlParameters[1] = new SqlParameter("@nammoi", SqlDbType.Int);
            sqlParameters[1].Value = nammoi;

            sqlParameters[2] = new SqlParameter("@tungay", SqlDbType.NVarChar);
            sqlParameters[2].Value = tungay;
            conn.executeInsertQuery(query, sqlParameters);

            query = string.Format("Update NT_KY set ky = 0,cuoiky = @denngay where thang = @thang and nam = @nam");
            sqlParameters = new SqlParameter[3];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = thang;

            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = nam;

            sqlParameters[2] = new SqlParameter("@denngay", SqlDbType.NVarChar);
            sqlParameters[2].Value = denngay;
            conn.executeInsertQuery(query, sqlParameters);


            
            return true;

            //string query = string.Format("insert into NT_PHIEUNHAP_KY "
            //+ " select stt_nh, ma_hieu, " + thang, nam + ","
            //+ " sum(sldauky) as sldauky,sum(slnhap) as slnhap,sum(slxuat)as slxuat, sum(slxuatdc) as slxuatdc, "
            //+ " sum(sldauky) + sum(slnhap) - sum(slxuat) - sum(slxuatdc) as slcuoiky "
            //+ " from ("
            //+ " select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, "
            //+ " a.duongdung as bietduoc,a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
            //+ " isnull(c.sldauky,0) as sldauky,isnull(sum(g.slnhap),0) as slnhap,0 as slxuat,"
            //+ " isnull(slxuatdc,0) as slxuatdc"
            //+ " from dm a left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1 "
            //+ " left join NT_PHIEUNHAP c on a.stt_nh = c.stt_nh and a.ma_hieu = c.ma_hieu and c.thang = " + thang + " and c.nam = " + nam
            //+ " left join NT_PHIEUNHAP_DTL g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = c.thang and g.nam = c.nam "
            //+ " and ngaynhap <= '" + denngay + "'"
            //+ " where  a.stt_nh in ('01','03') and a.chon = 1 and b.gia <> 0  "
            //+ " group by a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh, "
            //+ " c.sldauky,c.slnhap,slxuatdc,a.duongdung,a.lieudung"
            //+ " union all "
            //+ " select a.stt_nh, a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx, a.duongdung as bietduoc,"
            //+ " a.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
            //+ " 0 as sldauky,0 as slnhap,0 as slxuat,isnull(sum(g.slxuat),0)  as slxuatdc from dm a "
            //+ " left join gia b on a.stt_nh = b.stt_nh and a.ma_hieu = b.ma_hieu and a.chon = 1 "
            //+ " left join NT_PHIEUXUAT_DC g on a.stt_nh = g.stt_nh and a.ma_hieu = g.ma_hieu and g.thang = @thang and g.nam = @nam "
            //+ " and ngayxuat <= '" + denngay + "'"
            //+ " where  a.stt_nh in ('01','03') and a.chon = 1 and b.gia <> 0  group by a.stt_nh, "
            //+ " a.ma_hieu,a.ten_hieu,a.dvt,a.ham_luong,a.nuoc_sx,gia,gia_bhxh,a.duongdung,a.lieudung "
            //+ " union all "
            //+ " select a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,"
            //+ " d.duongdung as bietduoc,d.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh,"
            //+ " 0 as sldauky,0 slnhap,sum(sl) as slxuat,0 as slxuatdc"
            //+ " from p1_h b inner join p1_d a on a.ct_id = b.ct_id "
            //+ " inner join dm d on d.ma_hieu = a.ma_hieu  where  a.stt_nh in ('01','03') "
            //+ " and ngay_tt >= (select dauky from nt_ky where ky = 1) "
            //+ " and ngay_tt <= '" + denngay + "'"
            //+ " group by a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,gia,gia_bhxh,"
            //+ " d.duongdung,d.lieudung"
            //+ " union all "
            //+ " select a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,"
            //+ " d.duongdung as bietduoc,d.lieudung as nhomthuoc,isnull(gia,0) as gia,isnull(gia_bhxh,0) as gia_bhxh, "
            //+ " 0 as sldauky,0 slnhap,sum(sl) as slxuat,0 as slxuatdc "
            //+ " from p2_h b inner join p2_d a on a.ct_id = b.ct_id "
            //+ " inner join dm d on d.ma_hieu = a.ma_hieu  where  a.stt_nh in ('01','03') "
            //+ " and ngay_tt >= (select dauky from nt_ky where ky = 1) "
            //+ " and ngay_tt <= '" + denngay + "'"
            //+ " group by a.stt_nh,d.ma_hieu,d.ten_hieu,d.dvt,d.ham_luong,d.nuoc_sx,gia,gia_bhxh,"
            //+ " d.duongdung,d.lieudung"
            //+ " ) as temps"
            //+ " group by "
            //+ " stt_nh,ma_hieu,ten_hieu,dvt,ham_luong,nuoc_sx,gia,gia_bhxh,"
            //+ " bietduoc,nhomthuoc");
        }
        public bool Luiky(int thang, int nam)
        {
            int thangmoi = thang - 1;
            int nammoi = nam;
            if (thangmoi == 0)
            {
                thangmoi = 12;
                nammoi = nammoi - 1;
            }
            string query = string.Format("delete NT_PHIEUNHAP where thang = @thang and nam = @nam");
            SqlParameter[] sqlParameters = new SqlParameter[2];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = thang;

            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = nam;
            conn.executeInsertQuery(query, sqlParameters);

            /*up date phieu nhap*/
            query = string.Format("UPDATE NT_PHIEUNHAP_DTL SET THANG = @thangmoi,nam = @nammoi "
                + " where ngaynhap >= (select dauky from nt_ky where ky = 1) and thang = @thang and nam = @nam ");

            sqlParameters = new SqlParameter[4];
            sqlParameters[0] = new SqlParameter("@thangmoi", SqlDbType.Int);
            sqlParameters[0].Value = thangmoi;

            sqlParameters[1] = new SqlParameter("@nammoi", SqlDbType.Int);
            sqlParameters[1].Value = nammoi;

            sqlParameters[2] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[2].Value = thang;

            sqlParameters[3] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[3].Value = nam;
          
            conn.executeInsertQuery(query, sqlParameters);

            /*delete phieu nhap*/
            query = string.Format("delete NT_PHIEUNHAP_DTL where THANG = @thang and nam = @nam and dauky = 1 ");

            sqlParameters = new SqlParameter[2];
            sqlParameters[0] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[0].Value = thang;
            sqlParameters[1] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[1].Value = nam;
            
            conn.executeInsertQuery(query, sqlParameters);


            /*up date phieu xuat*/
            query = string.Format("UPDATE NT_PHIEUXUAT SET THANG = @thangmoi,nam = @nammoi "
                + " where ngay_xuat >= (select dauky from nt_ky where ky = 1) and thang = @thang and nam = @nam ");

            sqlParameters = new SqlParameter[4];
            sqlParameters[0] = new SqlParameter("@thangmoi", SqlDbType.Int);
            sqlParameters[0].Value = thangmoi;

            sqlParameters[1] = new SqlParameter("@nammoi", SqlDbType.Int);
            sqlParameters[1].Value = nammoi;

            sqlParameters[2] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[2].Value = thang;

            sqlParameters[3] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[3].Value = nam;

            conn.executeInsertQuery(query, sqlParameters);
            /*up date phieu dieu chinh*/

            query = string.Format("UPDATE NT_PHIEUXUAT_DC SET THANG = @thangmoi,nam = @nammoi "
               + " where ngayxuat >= (select dauky from nt_ky where ky = 1) and thang = @thang and nam = @nam ");

            sqlParameters = new SqlParameter[4];
            sqlParameters[0] = new SqlParameter("@thangmoi", SqlDbType.Int);
            sqlParameters[0].Value = thangmoi;

            sqlParameters[1] = new SqlParameter("@nammoi", SqlDbType.Int);
            sqlParameters[1].Value = nammoi;

            sqlParameters[2] = new SqlParameter("@thang", SqlDbType.Int);
            sqlParameters[2].Value = thang;

            sqlParameters[3] = new SqlParameter("@nam", SqlDbType.Int);
            sqlParameters[3].Value = nam;
            conn.executeInsertQuery(query, sqlParameters);

            query = string.Format("delete NT_KY where ky = 1");
            sqlParameters = new SqlParameter[0];
            conn.executeInsertQuery(query, sqlParameters);

            query = string.Format("Update NT_KY set ky = 1  where thang = @thangmoi and nam = @nammoi");
            sqlParameters = new SqlParameter[2];
            sqlParameters[0] = new SqlParameter("@thangmoi", SqlDbType.Int);
            sqlParameters[0].Value = thangmoi;

            sqlParameters[1] = new SqlParameter("@nammoi", SqlDbType.Int);
            sqlParameters[1].Value = nammoi;
            conn.executeInsertQuery(query, sqlParameters);



            return true;
        }
        public DataTable kiemtraDAO(string ngay,string sophieu)
        {
            string query = string.Format("select ho_ten,mathe,ngay_tt,sophieu,tongtien - tienct as sosanh,tongtien,tienct,'ngoaitru' as loai from "
               + " ( "
               + "  select ho_ten,mathe,ngay_tt,sophieu,sum(tienct) as tienct,sum(tongtien) as tongtien from "
               + "  ("
               + "  select ho_ten,mathe,ngay_tt,sophieu,tong_tien as tongtien,0 as tienct from p1_du a inner join p1_h b"
               + "  on a.ct_id = b.ct_id"
               + "  where ngay_tt >= @ngay and stt_nh in ('01','03') and sophieu = @sophieu "
               + "  union all"
               + "  select ho_ten,mathe,ngay_tt,sophieu,0 as tongtien,sum(gia*sl) as tienct from p1_d a inner join p1_h b"
               + "  on a.ct_id = b.ct_id"
               + "  where ngay_tt = @ngay and stt_nh in ('01','03') and sophieu = @sophieu "
               + "  group by sophieu,ho_ten,mathe,ngay_tt"
               + "  ) as tam"
               + "  group by sophieu,ho_ten,mathe,ngay_tt"
               + "  ) as tam1 where tongtien - tienct <> 0"
               + "  union all"
               + "  select ho_ten,mathe,ngay_tt,sophieu,tongtien - tienct as sosanh,tongtien,tienct,'noitru' as loai from"
               + "  ("
               + "  select ho_ten,mathe,ngay_tt,sophieu,sum(tienct) as tienct,sum(tongtien) as tongtien from"
               + "  ("
               + "  select ho_ten,mathe,ngay_tt,sophieu,tong_tien as tongtien,0 as tienct from p2_du a inner join p2_h b"
               + "  on a.ct_id = b.ct_id"
               + "  where ngay_tt >= @ngay and stt_nh in ('01','03') and sophieu = @sophieu  "
               + "  union all"
               + "  select ho_ten,mathe,ngay_tt,sophieu,0 as tongtien,sum(gia*sl) as tienct from p2_d a inner join p2_h b"
               + "  on a.ct_id = b.ct_id"
               + "  where ngay_tt = @ngay and stt_nh in ('01','03') and sophieu = @sophieu "
               + "  group by sophieu,ho_ten,mathe,ngay_tt"
               + "  ) as tam"
               + "  group by sophieu,ho_ten,mathe,ngay_tt"
               + "  ) as tam1 where tongtien - tienct <> 0"
               + "  order by ngay_tt desc");

            SqlParameter[] sqlParameters = new SqlParameter[2];

            sqlParameters[0] = new SqlParameter("@ngay", SqlDbType.DateTime);
            sqlParameters[0].Value = ngay;
            sqlParameters[1] = new SqlParameter("@sophieu", SqlDbType.NVarChar);
            sqlParameters[1].Value = sophieu;

            return conn.executeSelectQuery(query, sqlParameters);
        }
        public DataTable kiemtraDAO(string ngay)
        {
            string query = string.Format("select ho_ten,mathe,ngay_tt,sophieu,tongtien - tienct as sosanh,tongtien,tienct,'ngoaitru' as loai from "
               + " ( "
               + "  select ho_ten,mathe,ngay_tt,sophieu,sum(tienct) as tienct,sum(tongtien) as tongtien from "
               + "  ("
               + "  select ho_ten,mathe,ngay_tt,sophieu,tong_tien as tongtien,0 as tienct from p1_du a inner join p1_h b"
               + "  on a.ct_id = b.ct_id"
               + "  where ngay_tt >= @ngay and stt_nh in ('01','03') "
               + "  union all"
               + "  select ho_ten,mathe,ngay_tt,sophieu,0 as tongtien,sum(gia*sl) as tienct from p1_d a inner join p1_h b"
               + "  on a.ct_id = b.ct_id"
               + "  where ngay_tt >= @ngay and stt_nh in ('01','03')"
               + "  group by sophieu,ho_ten,mathe,ngay_tt"
               + "  ) as tam"
               + "  group by sophieu,ho_ten,mathe,ngay_tt"
               + "  ) as tam1 where tongtien - tienct <> 0"
               + "  union all"
               + "  select ho_ten,mathe,ngay_tt,sophieu,tongtien - tienct as sosanh,tongtien,tienct,'noitru' as loai from"
               + "  ("
               + "  select ho_ten,mathe,ngay_tt,sophieu,sum(tienct) as tienct,sum(tongtien) as tongtien from"
               + "  ("
               + "  select ho_ten,mathe,ngay_tt,sophieu,tong_tien as tongtien,0 as tienct from p2_du a inner join p2_h b"
               + "  on a.ct_id = b.ct_id"
               + "  where ngay_tt >= @ngay and stt_nh in ('01','03')  "
               + "  union all"
               + "  select ho_ten,mathe,ngay_tt,sophieu,0 as tongtien,sum(gia*sl) as tienct from p2_d a inner join p2_h b"
               + "  on a.ct_id = b.ct_id"
               + "  where ngay_tt >= @ngay and stt_nh in ('01','03')"
               + "  group by sophieu,ho_ten,mathe,ngay_tt"
               + "  ) as tam"
               + "  group by sophieu,ho_ten,mathe,ngay_tt"
               + "  ) as tam1 where tongtien - tienct <> 0"
               + "  order by ngay_tt desc");
            
            SqlParameter[] sqlParameters = new SqlParameter[1];

            sqlParameters[0] = new SqlParameter("@ngay", SqlDbType.NVarChar);
            sqlParameters[0].Value = ngay;

            return conn.executeSelectQuery(query, sqlParameters);
        }

    }
    
   
}
